package com.dao.qsl;

import com.util.qsl.Dbutil;
import org.springframework.stereotype.Repository;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class TongJi {

      /**
       * 查询业主name
       */
      public   List<String>  getyzinfo(){
	  Dbutil d=new Dbutil();

	  Connection c=null;

	  List<String> list=new ArrayList<>();

	  try{
	        c=d.getConnection();

	        String sql="SELECT Hname from household ";
	        PreparedStatement pr=c.prepareStatement(sql);

	        ResultSet rs=pr.executeQuery();

	        while(rs.next()){
		    list.add(rs.getString("Hname"));
	        }

	  }catch(Exception e){
	        e.printStackTrace();
	  }

	  return list;
      }

      /**
       * 物业选择业主查看缴费信息
       */
      public   List<String>    ckjf(String yzname){
	  Dbutil d=new Dbutil();

	  Connection c=null;

	  List<String> list=new ArrayList<String>();

	  int aid= getaid(yzname);
	  try {
	        c=d.getConnection();

	        for(int i=1;i<13;i++) {
		    String sql = "SELECT  aid,sum(s.s_money*d.D_sum)  FROM dateal  d join standard s on d.S_id = s.S_id WHERE aid= ?  and  year(d.D_billdate)=( SELECT YEAR(NOW())) and  month(d.D_billdate)=? and s_name like '%物业管理费%' ";

		    PreparedStatement pr = c.prepareStatement(sql);

		    pr.setInt(1, aid);
		    pr.setInt(2,i);

		    ResultSet rs = pr.executeQuery();

		    if (rs.next()) {
			if(rs.getString("sum(s.s_money*d.D_sum)")==null){
			      list.add("0");
			}else {
			      list.add(rs.getString("sum(s.s_money*d.D_sum)"));
			}
		    }

	        }



	  }catch(Exception e){
	        e.printStackTrace();
	  }
	  return list;

      }

      /**
       * 查看业主水费
       */
      public   List<String>    cksf(String yzname){
	  Dbutil d=new Dbutil();

	  Connection c=null;

	  List<String> list=new ArrayList<String>();

	  int aid= getaid(yzname);
	  try {
	        c=d.getConnection();

	        for(int i=1;i<13;i++) {
		    String sql = "SELECT  aid,sum(s.s_money*d.D_sum)  FROM dateal  d join standard s on d.S_id = s.S_id WHERE aid= ?  and  year(d.D_billdate)=( SELECT YEAR(NOW())) and  month(d.D_billdate)=? and s_name like '%水费' ";

		    PreparedStatement pr = c.prepareStatement(sql);

		    pr.setInt(1, aid);
		    pr.setInt(2,i);

		    ResultSet rs = pr.executeQuery();

		    if (rs.next()) {
			if(rs.getString("sum(s.s_money*d.D_sum)")==null){
			      list.add("0");
			}else {
			      list.add(rs.getString("sum(s.s_money*d.D_sum)"));
			}
		    }

	        }

	  }catch(Exception e){
	        e.printStackTrace();
	  }
	  return list;

      }

      /**
       * 查看业主电费
       */
      public   List<String>    ckdf(String yzname){
	  Dbutil d=new Dbutil();

	  Connection c=null;

	  List<String> list=new ArrayList<String>();

	  int aid= getaid(yzname);
	  try {
	        c=d.getConnection();

	        for(int i=1;i<13;i++) {
		    String sql = "SELECT  aid,sum(s.s_money*d.D_sum)  FROM dateal  d join standard s on d.S_id = s.S_id WHERE aid= ?  and  year(d.D_billdate)=( SELECT YEAR(NOW())) and  month(d.D_billdate)=? and s_name like '%电费' ";

		    PreparedStatement pr = c.prepareStatement(sql);

		    pr.setInt(1, aid);
		    pr.setInt(2,i);

		    ResultSet rs = pr.executeQuery();

		    if (rs.next()) {
			if(rs.getString("sum(s.s_money*d.D_sum)")==null){
			      list.add("0");
			}else {
			      list.add(rs.getString("sum(s.s_money*d.D_sum)"));
			}
		    }

	        }



	  }catch(Exception e){
	        e.printStackTrace();
	  }
	  return list;

      }



      /**
       * 根据name获取aid
       */
      public  int getaid(String yzname){
	  Dbutil d=new Dbutil();
	  Connection c=null;

	  int aid=0;
	  try {
	        c=d.getConnection();
	        String sql="SELECT aid from household WHERE Hname=?";
	        PreparedStatement pr=c.prepareStatement(sql);
	        pr.setString(1,yzname);

	        ResultSet rs=pr.executeQuery();

	        if(rs.next()){
		     aid=rs.getInt("aid");

	        }

	  }catch(Exception e){
	        e.printStackTrace();
	  }
	  return  aid;
      }

      /**
       * 业主查看物业费
       */
      public   List<String>    yzckwyf(HttpServletRequest request){

	  Dbutil d=new Dbutil();

	  Connection c=null;

	  List<String> list=new ArrayList<String>();
	  Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");
	  Integer   aid = (Integer) user.get("aid");
	  try {
	        c=d.getConnection();

	        for(int i=1;i<13;i++) {
		    String sql = "SELECT  aid,sum(s.s_money*d.D_sum)  FROM dateal  d join standard s on d.S_id = s.S_id WHERE aid= ?  and  year(d.D_billdate)=( SELECT YEAR(NOW())) and  month(d.D_billdate)=? and s_name  like '%物业管理费%' ";

		    PreparedStatement pr = c.prepareStatement(sql);

		    pr.setInt(1, aid);
		    pr.setInt(2,i);

		    ResultSet rs = pr.executeQuery();

		    if (rs.next()) {
			if(rs.getString("sum(s.s_money*d.D_sum)")==null){
			      list.add("0");
			}else {
			      list.add(rs.getString("sum(s.s_money*d.D_sum)"));
			}
		    }

	        }



	  }catch(Exception e){
	        e.printStackTrace();
	  }
	  return list;

      }

      /**
       * 业主查看电费
       */
      public   List<String>    yzckdf(HttpServletRequest request){

	  Dbutil d=new Dbutil();

	  Connection c=null;

	  List<String> list=new ArrayList<String>();
	  Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");
	  Integer   aid = (Integer) user.get("aid");
	  try {
	        c=d.getConnection();

	        for(int i=1;i<13;i++) {
		    String sql = "SELECT  aid,sum(s.s_money*d.D_sum)  FROM dateal  d join standard s on d.S_id = s.S_id WHERE aid= ?  and  year(d.D_billdate)=( SELECT YEAR(NOW())) and  month(d.D_billdate)=? and s_name like '%电费' ";

		    PreparedStatement pr = c.prepareStatement(sql);

		    pr.setInt(1, aid);
		    pr.setInt(2,i);

		    ResultSet rs = pr.executeQuery();

		    if (rs.next()) {
			if(rs.getString("sum(s.s_money*d.D_sum)")==null){
			      list.add("0");
			}else {
			      list.add(rs.getString("sum(s.s_money*d.D_sum)"));
			}
		    }

	        }



	  }catch(Exception e){
	        e.printStackTrace();
	  }
	  return list;

      }

      /**
       * 业主查看水费
       */
      public   List<String>    yzcksf(HttpServletRequest request){

	  Dbutil d=new Dbutil();

	  Connection c=null;

	  List<String> list=new ArrayList<String>();
	  Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");
	  Integer   aid = (Integer) user.get("aid");
	  try {
	        c=d.getConnection();

	        for(int i=1;i<13;i++) {
		    String sql = "SELECT  aid,sum(s.s_money*d.D_sum)  FROM dateal  d join standard s on d.S_id = s.S_id WHERE aid= ?  and  year(d.D_billdate)=( SELECT YEAR(NOW())) and  month(d.D_billdate)=? and s_name like '%水费' ";

		    PreparedStatement pr = c.prepareStatement(sql);

		    pr.setInt(1, aid);
		    pr.setInt(2,i);

		    ResultSet rs = pr.executeQuery();

		    if (rs.next()) {
			if(rs.getString("sum(s.s_money*d.D_sum)")==null){
			      list.add("0");
			}else {
			      list.add(rs.getString("sum(s.s_money*d.D_sum)"));
			}
		    }

	        }



	  }catch(Exception e){
	        e.printStackTrace();
	  }
	  return list;

      }
}
